from crypt import methods
import string
from flask import Flask, g, request,current_app,jsonify
import sqlite3
import click
from flask.cli import with_appcontext

def get_db():
    if 'db' not in g:
        g.db = sqlite3.connect(
            "tutorial.db",
            detect_types=sqlite3.PARSE_DECLTYPES
        )
#        g.db.row_factory = sqlite3.Row

    return g.db


def close_db(e=None):
    db = g.pop('db', None)

    if db is not None:
        db.close()

# def init_db():
#     db = get_db()

#     with current_app.open_resource('schema.sql') as f:
#         db.executescript(f.read().decode('utf8'))


# @click.command('init-db')
# @with_appcontext
# def init_db_command():
#     """Clear the existing data and create new tables."""
#     init_db()
#     click.echo('Initialized the database.')

# def init_app(app):
#     app.teardown_appcontext(close_db)
#     app.cli.add_command(init_db_command)

app = Flask(__name__)

#init_app(app)
# 分页查询ranks中的昵称和分数


@app.route('/api/leaderboard',methods=["GET"])
def getLeaderboard():
    # 连接数据库
    con = get_db()
    cur = con.cursor()
    # name = request.args['username']
    cur.execute("""SELECT username, score, achievements, submit_date
         FROM leaderboard order by score desc limit ?,?""", (0, 20))
    list = cur.fetchall()
    ranks = []
    for i in range(len(list)):
        act = dict()
        act['username'] = list[i][0]
        act['score'] = list[i][1]
        act['achievements'] = list[i][2]
        act['submitDate'] =list[i][3]
        act['ranks'] = i+1
        ranks.append(act)
        
    cur.execute("SELECT count(*) FROM leaderboard ")
    return ranks

# 更新用户的最高分
@app.route('/api/currentboard',methods=["GET"])
def geCurretboard():
    # 连接数据库
    con = get_db()
    cur = con.cursor()
    # name = request.args['username']
    cur.execute("""SELECT username, score, achievements, submit_date
         FROM leaderboard order by id desc limit ?,?""", (0, 20))
    list = cur.fetchall()
    ranks = []
    for i in range(len(list)):
        act = dict()
        act['username'] = list[i][0]
        if(len(act['username'])>30):
            act['username'] = act['username'][:30]
        act['score'] = list[i][1]
        act['achievements'] = list[i][2]
        act['submitDate'] =list[i][3]
        act['ranks'] = i+1
        ranks.append(act)
        
    cur.execute("SELECT count(*) FROM leaderboard ")
    return ranks

@app.route('/api/submitScore',methods=["POST"])
def postScore():
    # 连接数据库
    con = get_db()
    cur = con.cursor()
    submit = request.get_json()
    cur.execute("INSERT INTO leaderboard (username, score, achievements, submit_date) VALUES (?,?,?,datetime(CURRENT_TIMESTAMP,'localtime'))",
                (submit['username'], submit['score'], submit['achievements']))
    con.commit()
    return "ok"

@app.route('/api/submitFeedback',methods=["POST"])
def postFeedback():
    con = get_db()
    cur = con.cursor()
    submit = request.get_json()
    
    res = cur.execute("select COUNT (*) from feedback")
    k = res.fetchone()
    if(k[0]<1000):
        cur.execute("INSERT INTO feedback (contents) VALUES (?)", (submit['content'],))
        con.commit()
    return "received {} feedbacks".format(k[0])

@app.route('/api/hello', methods=['GET'])
def hello():
    return "<p>hello,world</p>"